package com.mikeli.javacourse.lesson5.database;

import cn.hutool.core.util.IdUtil;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.*;
import java.util.Random;
import java.util.stream.IntStream;

public class JdbcConnectionTest {
    static String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8";
    static String user = "root";
    static String pwd = "123456";
    static String[] name={"张三","李四","王五"};

    public static void main(String[] args) throws SQLException {
        tansactionTest();
        jdbcTest();
        ConnectionPoolTest();
    }

    private static void ConnectionPoolTest() throws SQLException {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(url);
        hikariConfig.setUsername(user);
        hikariConfig.setPassword(pwd);
        hikariConfig.setMaximumPoolSize(20);
        HikariDataSource dataSource = new HikariDataSource(hikariConfig);
        Connection connection=null;
        PreparedStatement statement =null;
        ResultSet result =null;
        try{
             connection = dataSource.getConnection();
             statement = connection.prepareStatement("select * from test.t_test");
             result = statement.executeQuery();
            ResultSetMetaData meta = result.getMetaData();
            for (int i = 0; i < meta.getColumnCount(); i++) {
                System.out.print(meta.getColumnName(i+1) + "\t\t\t\t\t\t\t");
            }
            System.out.println();
            while (result.next()){
                for (int i = 0; i < meta.getColumnCount(); i++) {
                    System.out.print(result.getString(meta.getColumnName(i+1)) + "\t");
                }
                System.out.println();
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (result!= null)
                result.close();
            if (connection != null)
                connection.close();
        }
        dataSource.close();
    }

    private static void tansactionTest() throws SQLException {
        ResultSet result=null;
        Connection connection = null;
        PreparedStatement statement = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url,user,pwd);
            connection.setAutoCommit(false);
            statement = connection.prepareStatement("insert into test.t_test values(?,?,?,?)");

            PreparedStatement finalStatement = statement;
            PreparedStatement finalStatement1 = statement;
            IntStream.range(0, 10).forEach(i->{
                try {
                    if(i==100){
                        // 测试异常回滚
                       // if(i==99){
                        finalStatement.setString(1, IdUtil.fastUUID() + IdUtil.fastUUID());
                    } else {
                        finalStatement.setString(1, IdUtil.simpleUUID());
                    }
                    finalStatement.setString(2,name[new Random().nextInt(3)]);
                    finalStatement.setString(3,String.valueOf(new Random().nextInt(20)));
                    finalStatement.setString(4,String.valueOf(new Random().nextInt(20)));
                    finalStatement1.addBatch();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });

            statement.executeBatch();
            connection.commit();
        }catch (Exception e){
            e.printStackTrace();
            connection.rollback();
        }finally {
            if(result!=null){
                result.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
    }


    /**
     *
     * @throws SQLException
     */
    public static void jdbcTest() throws SQLException {
        ResultSet result=null;
        Connection connection = null;
        Statement statement = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url,user,pwd);
            statement = connection.createStatement();
            result = statement.executeQuery("select * from test.t_test");
            ResultSetMetaData meta = result.getMetaData();
            for (int i = 0; i < meta.getColumnCount(); i++) {
                System.out.print(meta.getColumnName(i+1) + "\t\t\t\t\t\t\t");
            }
            System.out.println();
            while (result.next()){
                for (int i = 0; i < meta.getColumnCount(); i++) {
                    System.out.print(result.getString(meta.getColumnName(i+1)) + "\t");
                }
                System.out.println();
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(result!=null){
                result.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
    }
}
